package org.i_plus.model.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Vector;

import org.i_plus.model.FriendBean;
import org.i_plus.model.FriendDAO;
import org.i_plus.model.MemberBean;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class FriendDAOJdbc implements FriendDAO {
	private static final String URL = "jdbc:sqlserver://localhost:1433;database=LovePlusPlus";
	private static final String USERNAME = "sa";
	private static final String PASSWORD = "passw0rd";

	private static final String SELECT_BY_ID = "Select fromacc , "
			+ "toacc , situation from Friends where fromacc=? and toacc=? ";

	public FriendBean select(String fromacc, String toacc) {
		FriendBean result = null;

		Connection conn = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;

		try {
			conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
			pstm = conn.prepareStatement(SELECT_BY_ID);

			pstm.setString(1, fromacc);
			pstm.setString(2, toacc);

			rs = pstm.executeQuery();

			while (rs.next()) {
				result = new FriendBean();
				result.setFromacc(rs.getString("fromacc"));
				result.setToacc(rs.getString("toacc"));
				result.setSituation(rs.getString("situation"));

			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException se) {
					se.printStackTrace(System.err);
				}
			}
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException se) {
					se.printStackTrace(System.err);
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
		}
		return result;
	}

	private static final String SELECT_ALL = "Select fromacc , toacc , situation from Friends  ";

	public List select() {
		List result = null;

		Connection conn = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;

		try {
			conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
			pstm = conn.prepareStatement(SELECT_ALL);
			rs = pstm.executeQuery();
			result = new Vector();
			while (rs.next()) {
				FriendBean temp = new FriendBean();

				temp.setFromacc(rs.getString("fromacc"));
				temp.setToacc(rs.getString("toacc"));
				temp.setSituation(rs.getString("situation"));

				result.add(temp);

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException se) {
					se.printStackTrace(System.err);
				}
			}
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException se) {
					se.printStackTrace(System.err);
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
		}
		return result;
	}

	private static final String INSERT = "Insert into Friends(fromacc , toacc , situation)values(?,?,?)";

	public void insertFriend(FriendBean bean) {

		Connection conn = null;
		PreparedStatement pstm = null;

		try {
			conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
			pstm = conn.prepareStatement(INSERT);

			pstm.setString(1, bean.getFromacc());
			pstm.setString(2, bean.getToacc());
			pstm.setString(3, bean.getSituation());

			pstm.executeUpdate();

		} catch (RuntimeException | SQLException e) {
			e.printStackTrace();
		} finally {
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException se) {
					se.printStackTrace(System.err);
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
		}

	}

	private static final String UPDATE = "Update Friends set situation=? where fromacc=? and toacc=?";

	public void update(FriendBean bean) {

		FriendBean result = null;

		Connection conn = null;
		PreparedStatement pstm = null;

		try {
			conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
			pstm = conn.prepareStatement(UPDATE);

			pstm.setString(1, bean.getSituation());
			pstm.setString(2, bean.getFromacc());
			pstm.setString(3, bean.getToacc());

			int i = pstm.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException se) {
					se.printStackTrace(System.err);
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
		}

	}

	private static final String DELETE = "Delete from Friends where fromacc = ? and toacc = ? ";

	public void delete(String fromacc, String toacc) {
		int result = 0;

		Connection conn = null;
		PreparedStatement pstm = null;

		try {
			conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
			pstm = conn.prepareStatement(DELETE);

			pstm.setString(1, fromacc);
			pstm.setString(2, toacc);
			result = pstm.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException se) {
					se.printStackTrace(System.err);
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
		}

	}

	private static final String SELECT_BYFROMACC = "  SELECT friend.toacc, member.pic1 from Friends friend,Members member where (friend.fromacc=?)and(friend.situation='t') and member.account=friend.toacc ";
	public List<MemberBean> selectByFromacc(String fromacc){
		Connection conn = null;
		PreparedStatement pstm = null;
		List<MemberBean > result = new ArrayList<MemberBean>();
		try {
			conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
			pstm = conn.prepareStatement(SELECT_BYFROMACC);
			pstm.setString(1, fromacc);
			ResultSet rs = pstm.executeQuery();
			while(rs.next()){
				MemberBean bean = new MemberBean();
				bean.setAccount(rs.getString(1));
				bean.setPic1(rs.getBytes(2));
				result.add(bean);
			}
			return result;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException se) {
					se.printStackTrace(System.err);
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
		}		
		return null;
	}
	
	public static void main(String[] args) {
		//FriendDAOJdbc dao = new FriendDAOJdbc();
		
		ApplicationContext context = new ClassPathXmlApplicationContext(
				"beans.config.xml");

		FriendDAOJdbc dao = (FriendDAOJdbc) context
				.getBean("FriendDAO");
		

//		 FriendBean bean = new FriendBean();
//		 bean.setFromacc("BB");
//		 bean.setToacc("BB");
//		 bean.setSituation("2");
//		 dao.insertFriend(bean);

		// System.out.println("getFromacc" + bean.getFromacc());
		// System.out.println("getToacc" + bean.getToacc());
		// System.out.println("getSituation" + bean.getSituation());
		// 看有沒有執行到這，並把它抓出來顯示。

//		 System.out.println("done");

//		 FriendBean bean1 = new FriendBean();
//		 bean1.setFromacc("BB");
//		 bean1.setToacc("BB");
//		 bean1.setSituation("5");
//		 dao.update(bean1);
//		 System.out.println("done"); 

//		 dao.delete("BB" , "BB");
//		 System.out.println("done"); 

//		 FriendBean bean2 = dao.select("BB" ,"BB");
//		 System.out.println(bean2.getFromacc() + ",");
//		 System.out.println(bean2.getToacc() + ",");
//		 System.out.println(bean2.getSituation() + ",");
//		 System.out.println("done"); 

//		 List<FriendBean> list = dao.select();
//		 System.out.println(list);
//		 for (FriendBean alist : list) {
//		 System.out.println(alist.getFromacc() + ",");
//		 System.out.println(alist.getToacc() + ",");
//		 System.out.println(alist.getSituation() + ",");
//		 System.out.println("done");
//		
//		 }
	}

}
